package org.exp3;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ScDao {
    private static final String URL = "jdbc:mysql://localhost:3306/experiment3?useSSL=false&serverTimezone=UTC";
    private static final String USER = "mysql";
    private static final String PASS = "050601";

    private CouDao coudao = new CouDao();

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.err.println("MySQL JDBC 驱动未找到！");
            e.printStackTrace();
        }
    }


    public void addSC(SC sc) throws SQLException {
        String sql = "INSERT INTO sc (stuid, couid, grade) VALUES (?, ?, ?)";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, sc.getStuid());
            ps.setInt(2, sc.getCouid());
            ps.setFloat(3, sc.getGrade());
            ps.executeUpdate();
            System.out.println("选课记录添加成功！");
        } catch (SQLException e) {
            logSQLException(e, "添加选课记录失败");
            throw new SQLException("添加选课记录失败", e);
        }
    }

    public boolean deleteSC(int stuId, int couId) {
        String sql = "DELETE FROM sc WHERE stuid = ? AND couid = ?";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, stuId);
            ps.setInt(2, couId);
            int rows = ps.executeUpdate();
            if (rows > 0) {
                System.out.println("选课记录删除成功！");
                return true;
            } else {
                System.out.println("未找到匹配的选课记录（stuid=" + stuId + ", couid=" + couId + "），删除失败。");
                return false;
            }
        } catch (SQLException e) {
            logSQLException(e, "删除选课记录失败");
            return false; // 或抛出异常，根据设计决定
        }
    }

    // 获取所有选课记录
    public List<course_t> getAllSC() {
        List<course_t> list = new ArrayList<>();
        String sql = "SELECT s.stuid, s.stuname, c.couid, c.couname, sc.grade FROM sc "
                + "JOIN student s ON sc.stuid = s.stuid "
                + "JOIN course c ON sc.couid = c.couid";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                course_t sc = new course_t(
                        rs.getInt("stuid"),
                        rs.getString("stuname"),
                        rs.getInt("couid"),
                        rs.getString("couname"),
                        rs.getFloat("grade")
                );
                list.add(sc);
            }
        } catch (SQLException e) {
            logSQLException(e, "查询所有选课记录失败");
        }
        return list;
    }

    // 按课程ID查询
    public List<course_t> getSCByCourseId(int courseId) {
        List<course_t> list = new ArrayList<>();
        String sql = "SELECT s.stuid, s.stuname, c.couid, c.couname, sc.grade FROM sc "
                + "JOIN student s ON sc.stuid = s.stuid "
                + "JOIN course c ON sc.couid = c.couid "
                + "WHERE c.couid = ?";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, courseId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    course_t sc = new course_t(
                            rs.getInt("stuid"),
                            rs.getString("stuname"),
                            rs.getInt("couid"),
                            rs.getString("couname"),
                            rs.getFloat("grade")
                    );
                    list.add(sc);
                }
            }
        } catch (SQLException e) {
            logSQLException(e, "按课程ID查询选课记录失败");
        }
        return list;
    }

    // 按学生ID查询
    public List<course_t> getSCByStudentId(int studentId) {
        List<course_t> list = new ArrayList<>();
        String sql = "SELECT s.stuid, s.stuname, c.couid, c.couname, sc.grade FROM sc "
                + "JOIN student s ON sc.stuid = s.stuid "
                + "JOIN course c ON sc.couid = c.couid "
                + "WHERE s.stuid = ?";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, studentId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    course_t sc = new course_t(
                            rs.getInt("stuid"),
                            rs.getString("stuname"),
                            rs.getInt("couid"),
                            rs.getString("couname"),
                            rs.getFloat("grade")
                    );
                    list.add(sc);
                }
            }
        } catch (SQLException e) {
            logSQLException(e, "按学生ID查询选课记录失败");
        }
        return list;
    }

    public boolean updateSC(SC sc) throws SQLException {
        String sql = "UPDATE sc SET grade = ? WHERE stuid = ? AND couid = ?";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setFloat(1, sc.getGrade());
            ps.setInt(2, sc.getStuid());
            ps.setInt(3, sc.getCouid());
            int rowsAffected = ps.executeUpdate();
            if (rowsAffected > 0) {
                System.out.println("成绩更新成功！");
                return true;
            } else {
                System.out.println("未找到匹配的选课记录（stuid=" + sc.getStuid() + ", couid=" + sc.getCouid() + "），更新失败。");
                return false;
            }
        } catch (SQLException e) {
            logSQLException(e, "更新成绩失败");
            throw new SQLException("更新成绩失败", e);
        }
    }


    public SC getSCByStudentIdAndCourseId(int studentId, int courseId) {
        SC sc = null;
        String sql = "SELECT stuid, couid, grade FROM sc WHERE stuid = ? AND couid = ?";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, studentId);
            ps.setInt(2, courseId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    sc = new SC(
                            rs.getInt("stuid"),
                            rs.getInt("couid"),
                            rs.getFloat("grade")
                    );
                }
            }
        } catch (SQLException e) {
            logSQLException(e, "按学生ID和课程ID查询选课记录失败");
        }
        return sc;
    }

    // 统一日志方法（不暴露密码）
    private void logSQLException(SQLException e, String context) {
        System.err.println("=== " + context + " ===");
        System.err.println("错误消息: " + e.getMessage());
        System.err.println("SQL 状态码: " + e.getSQLState());
        System.err.println("错误代码: " + e.getErrorCode());
        e.printStackTrace();
    }
}